package com.seek.inter;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import com.alibaba.fastjson.JSONObject;
import com.seek.common.handler.SpringContextHolder;
import com.seek.model.dto.ResultInfo;
import com.seek.service.ReadService;
import com.seek.util.CommonUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 查询统计相关接口
 *
 */
public class ReportInter {
	private ReadService readService = (ReadService)SpringContextHolder.getBean("readService");
	private static final Logger logger = LoggerFactory.getLogger(ReportInter.class);


	/**
	 * 应收款查询
	 * 
	 * @param request
	 * @return
	 * @throws Exception
	 */
	public ResultInfo queryLoanOfReceivables(HttpServletRequest request, JSONObject reqJson) throws Exception {
		ResultInfo result = new ResultInfo();
		Long companyId = reqJson.getLong("companyId");
		String custPhone = reqJson.getString("custPhone");
		String rqq = reqJson.getString("rqq");
		String rqz = reqJson.getString("rqz");
		int delayDays = CommonUtil.getIntValue(reqJson.getLong("delayDays"));
		int excludeBad = CommonUtil.getIntValue(reqJson.getLong("excludeBad"));
		int excludeToday = CommonUtil.getIntValue(reqJson.getLong("excludeToday"));

		// 拼接检索条件
		StringBuffer where = new StringBuffer("");
		List<Object> params = new ArrayList<Object>();
		params.add(companyId);
		if (!CommonUtil.isEmpty(custPhone)) {
			custPhone = "%" + custPhone + "%";
			where.append(" and b.phone_no like ?");
			params.add(custPhone);
		}
		if (!CommonUtil.isEmpty(rqq)) {
			where.append(" and a.end_time > ?");
			params.add(rqq);
		}
		if (!CommonUtil.isEmpty(rqz)) {
			rqz = rqz.substring(0, 10) + " 23:59:59";
			where.append(" and a.end_time <= ?");
			params.add(rqz);
		}
		if (delayDays > 0) {
			where.append(" and datediff(now(),a.end_time)>= ?");
			params.add(delayDays);
		}
		//excludeToday 剔除当日放款及还款数据
		if (excludeToday > 0) {
			if (excludeBad > 0) {
				where.append(" and (a.loan_status=3 or (a.loan_status = 5 and DATE_FORMAT(a.payback_date,'%Y-%m-%d')=DATE_FORMAT(now(),'%Y-%m-%d')))");
			}else{
				where.append(" and (a.loan_status in (3,6) or (a.loan_status = 5 and DATE_FORMAT(a.payback_date,'%Y-%m-%d')=DATE_FORMAT(now(),'%Y-%m-%d')))");
			}
			where.append(" and DATE_FORMAT(a.down_time,'%Y-%m-%d') !=DATE_FORMAT(now(),'%Y-%m-%d')");
		}else{

			if (excludeBad > 0) {
				where.append(" and a.loan_status=3");
			}else{
				where.append(" and a.loan_status in (3,6)");
			}

		}

		// 取得总记录行数
		String sql = "select count(1) cnt from t_loan a, t_customer b where a.customer_id=b.id and a.company_id=?"
				+ where.toString();
		// 列表数据SQL: 把最需要审核的放在前面
		StringBuffer sb = new StringBuffer("");
		if (CommonUtil.getIntValue(reqJson.getString("hidePhone")) > 0)
			sb.append(
					"select a.id loanId,REPLACE(b.phone_no, SUBSTR(b.phone_no,4,4), '****') custPhone,b.name custName,a.req_money reqMoney,a.start_time startTime,");
		else
			sb.append(
					"select a.id loanId,b.phone_no custPhone,b.name custName,a.req_money reqMoney,a.start_time startTime,");
		sb.append(" a.end_time endTime,a.req_date reqTime,(case when a.loan_status=6 then 1 else 0 end) isBad,")
				.append(" datediff(now(),a.end_time) delayDays,ifnull(a.late_fee - a.off_latefee,0) lateFee,")
				.append(" (select u.user_name from t_sys_user u where a.overduer_id=u.id) overduerName,a.last_overdue_time lastOverdueTime,")
				.append(" (case when a.auditor_id=-1 then '系统审核' else (select u.user_name from t_sys_user u where a.auditor_id=u.id) end) auditName")
				.append(" from t_loan a, t_customer b where a.customer_id=b.id and a.company_id=?");
		sb.append(where).append(" order by a.id asc");

		result.setStatus(0);
		result.setMessage("成功");
		result.setBody(readService.pageQueryBySql(sb.toString(), sql, reqJson.getIntValue("pageSize"),
				reqJson.getIntValue("pageNo"), params.toArray()));
		return result;
	}

	/**
	 * 已收款查询
	 * 
	 * @param request
	 * @return
	 * @throws Exception
	 */
	public ResultInfo queryLoanOfRepayment(HttpServletRequest request, JSONObject reqJson) throws Exception {
		ResultInfo result = new ResultInfo();
		Long companyId = reqJson.getLong("companyId");
		String custPhone = reqJson.getString("custPhone");
		String rqq = reqJson.getString("rqq");
		String rqz = reqJson.getString("rqz");
		String repayStatus = reqJson.getString("repayStatus");
		String payType = reqJson.getString("payType");

		// 拼接检索条件
		StringBuffer where = new StringBuffer("");
		List<Object> params = new ArrayList<Object>();
		params.add(companyId);
		if (!CommonUtil.isEmpty(custPhone)) {
			custPhone = "%" + custPhone + "%";
			where.append(" and b.phone_no like ?");
			params.add(custPhone);
		}
		if (CommonUtil.getIntValue(repayStatus)==2) {
			if (!CommonUtil.isEmpty(rqq)) {
				where.append(" and a.create_time > ?");
				params.add(rqq);
			}
			if (!CommonUtil.isEmpty(rqz) && rqz.length()>=10) {
				rqz = rqz.substring(0, 10) + " 23:59:59";
				where.append(" and a.create_time < ?");
				params.add(rqz);
			}
		}else{
			if (!CommonUtil.isEmpty(rqq)) {
				where.append(" and a.pay_time > ?");
				params.add(rqq);
			}
			if (!CommonUtil.isEmpty(rqz) && rqz.length()>=10) {
				rqz = rqz.substring(0, 10) + " 23:59:59";
				where.append(" and a.pay_time < ?");
				params.add(rqz);
			}
		}
		
		if (CommonUtil.getIntValue(repayStatus)>0) {
			where.append(" and a.pay_status = ?");
			params.add(CommonUtil.getIntValue(repayStatus));
		}
		if (!CommonUtil.isEmpty(payType)) {
			where.append(" and a.pay_type = ?");
			params.add(CommonUtil.getIntValue(payType));
		}

		// 取得总记录行数
		String sql = "select count(1) cnt from t_loan_repayment a,t_customer b,t_loan c where a.loan_id=c.id and c.customer_id=b.id and a.company_id=?"
				+ where.toString();
		// 列表数据SQL: 把最需要审核的放在前面
		StringBuffer sb = new StringBuffer("select *, CASE type when '2' THEN payMoney - latefee ")
				.append("ELSE 0 END AS balance,CASE type when '1' THEN payMoney - latefee ELSE 0 ")
				.append("END AS interest from (");

		if (CommonUtil.getIntValue(reqJson.getString("hidePhone")) > 0)
			sb.append(
					"select a.id repayId,REPLACE(b.phone_no, SUBSTR(b.phone_no,4,4), '****') custPhone,b.name custName,a.pay_no repayNo,ifnull(a.pay_money,0) payMoney,ifnull(a.latefee,0) latefee, a.pay_time payTime,c.start_time startTime,c.end_time endTime ,a.type,a.pay_type payType,a.pay_status payStatus");
		else
			sb.append(
					"select a.id repayId,b.phone_no custPhone,b.name custName,a.pay_no repayNo,ifnull(a.pay_money,0) payMoney,ifnull(a.latefee,0) latefee,a.pay_time payTime,c.start_time startTime,c.end_time endTime ,a.type,a.pay_type payType,a.pay_status payStatus");
		sb.append(
				" from t_loan_repayment a,t_customer b,t_loan c where a.loan_id=c.id and c.customer_id=b.id and a.company_id=?")
				.append(where).append(" order by a.id asc ) r");

		result.setStatus(0);
		result.setMessage("成功");
		result.setBody(readService.pageQueryBySql(sb.toString(), sql, reqJson.getIntValue("pageSize"),
				reqJson.getIntValue("pageNo"), params.toArray()));
		return result;
	}

	/**
	 * 坏帐查询
	 * 
	 * @param request
	 * @return
	 * @throws Exception
	 */
	public ResultInfo queryLoanOfBad(HttpServletRequest request, JSONObject reqJson) throws Exception {
		ResultInfo result = new ResultInfo();
		Long companyId = reqJson.getLong("companyId");
		String custPhone = reqJson.getString("custPhone");
		String rqq = reqJson.getString("rqq");
		String rqz = reqJson.getString("rqz");

		// 拼接检索条件
		StringBuffer where = new StringBuffer("");
		List<Object> params = new ArrayList<Object>();
		params.add(companyId);
		if (!CommonUtil.isEmpty(custPhone)) {
			custPhone = "%" + custPhone + "%";
			where.append(" and b.phone_no like ?");
			params.add(custPhone);
		}
		if (!CommonUtil.isEmpty(rqq)) {
			where.append(" and a.end_time > ?");
			params.add(rqq);
		}
		if (!CommonUtil.isEmpty(rqz)) {
			rqz = rqz.substring(0, 10) + " 23:59:59";
			where.append(" and a.end_time <= ?");
			params.add(rqz);
		}

		// 取得总记录行数
		String sql = "select count(1) cnt from t_loan a, t_customer b where a.customer_id=b.id and a.company_id=? and a.loan_status=6"
				+ where.toString();
		// 列表数据SQL: 把最需要审核的放在前面
		StringBuffer sb = new StringBuffer("");
		if (CommonUtil.getIntValue(reqJson.getString("hidePhone")) > 0)
			sb.append(
					"select a.id loanId,REPLACE(b.phone_no, SUBSTR(b.phone_no,4,4), '****') custPhone,b.name custName,a.req_money reqMoney,a.start_time startTime,");
		else
			sb.append(
					"select a.id loanId,b.phone_no custPhone,b.name custName,a.req_money reqMoney,a.start_time startTime,");
		sb.append(" a.end_time endTime,a.req_date reqTime,(case when a.loan_status=6 then 1 else 0 end) isBad,")
				.append(" datediff(now(),a.end_time) delayDays,ifnull(a.late_fee - a.off_latefee,0) lateFee,")
				.append(" (select u.user_name from t_sys_user u where a.overduer_id=u.id) overduerName,a.last_overdue_time lastOverdueTime,")
				.append(" (case when a.auditor_id=-1 then '系统审核' else (select u.user_name from t_sys_user u where a.auditor_id=u.id) end) auditName,")
				.append(" (select count(1) from t_loan_overdue_log u where u.loan_id=a.id) overdueLogNum")
				.append(" from t_loan a, t_customer b where a.customer_id=b.id and a.company_id=? and a.loan_status=6");
		sb.append(where).append(" order by a.id asc");

		result.setStatus(0);
		result.setMessage("成功");
		result.setBody(readService.pageQueryBySql(sb.toString(), sql, reqJson.getIntValue("pageSize"),
				reqJson.getIntValue("pageNo"), params.toArray()));
		return result;
	}

	/**
	 * 代理商佣金报表
	 * 
	 * @param request
	 * @return
	 * @throws Exception
	 */
	public ResultInfo queryAgentMoney(HttpServletRequest request, JSONObject reqJson) throws Exception {
		ResultInfo result = new ResultInfo();
		Long companyId = reqJson.getLong("companyId");
		String rqq = reqJson.getString("rqq");
		String rqz = reqJson.getString("rqz");

		if(companyId == null || companyId<1 || CommonUtil.isEmpty(rqq) || CommonUtil.isEmpty(rqz)) {
			result.setStatus(1);
			result.setMessage("参数错误，请检查！");
			return result;
		} 
		rqz = rqz.substring(0,10)+" 23:59:59";
		
		StringBuffer sb = new StringBuffer("select b.id agentId,b.agent_name agentName,b.money_type moneyType,ifnull(a.regNum,0) regNum,ifnull(a.downNum,0) downNum,ifnull(a.downMoney,0) downMoney,");
		sb.append("ifnull(a.delayNum,0) delayNum,ifnull(a.delayMoney,0) delayMoney,ifnull(a.badNum,0) badNum,ifnull(a.badMoney,0) badMoney,ifnull(a.money,0) money from t_company_agent b left join (")
				.append(" select a.agentId, sum(a.regNum) regNum,sum(a.downNum) downNum,sum(a.downMoney) downMoney,")
				.append(" sum(a.delayNum) delayNum,sum(a.delayMoney) delayMoney,sum(a.badNum) badNum,sum(a.badMoney) badMoney,sum(a.money) money from (")
				.append(" select a.agent_id agentId, count(1) regNum, 0 downNum,0 downMoney, 0 delayNum, 0 delayMoney, 0 badNum, 0 badMoney,0 money")
				.append(" from t_customer a where a.company_id=? and a.active_date>=? and a.active_date<=?")
				.append(" group by a.agent_id").append(" union all")
				.append(" select a.sub_agent_id agentId, count(1) regNum, 0 downNum,0 downMoney, 0 delayNum, 0 delayMoney, 0 badNum, 0 badMoney,0 money")
				.append(" from t_customer a where a.company_id=? and a.active_date>=? and a.active_date<=?")
				.append(" group by a.sub_agent_id").append(" union all")
				.append(" select a.agent_id agentId,0 regNum, count(1) downNum, sum(a.req_money) downMoney,")
				.append(" sum((case when a.loan_status in (3,6) and now()>a.end_time then 1 when a.loan_status=5 and a.payback_date>now() then 1 else 0 end)) delayNum,")
				.append(" sum((case when a.loan_status in (3,6) and now()>a.end_time then a.req_money when a.loan_status=5 and a.payback_date>now() then a.req_money else 0 end)) delayMoney,")
				.append(" sum((case when a.loan_status=6 then 1 else 0 end)) badNum,")
				.append(" sum((case when a.loan_status=6 then a.req_money else 0 end)) badMoney, 0 money")
				.append(" from t_loan a where a.company_id=? and a.down_time>=? and a.down_time<=? group by a.agent_id")
				.append(" union all")
				.append(" select a.sub_agent_id agentId,0 regNum, count(1) downNum, sum(a.req_money) downMoney,")
				.append(" sum((case when a.loan_status in (3,6) and now()>a.end_time then 1 when a.loan_status=5 and a.payback_date>now() then 1 else 0 end)) delayNum,")
				.append(" sum((case when a.loan_status in (3,6) and now()>a.end_time then a.req_money when a.loan_status=5 and a.payback_date>now() then a.req_money else 0 end)) delayMoney,")
				.append(" sum((case when a.loan_status=6 then 1 else 0 end)) badNum,")
				.append(" sum((case when a.loan_status=6 then a.req_money else 0 end)) badMoney, 0 money")
				.append(" from t_loan a where a.company_id=? and a.down_time>=? and a.down_time<=? group by a.sub_agent_id")
				.append(" union all")
				.append(" select a.agent_id agentId,0 regNum, 0 downNum,0 downMoney, 0 delayNum, 0 delayMoney, 0 badNum, 0 badMoney,sum(a.money+ifnull(sub_agent_money,0)) money")
				.append(" from t_company_agent_money a where a.create_time>=? and a.create_time<=? group by a.agent_id) a group by a.agentId) a on b.id=a.agentId and b.company_id=1 and b.is_used=1 order by b.id asc");
		Object[] params = new Object[] { companyId, rqq, rqz, companyId, rqq, rqz,companyId, rqq, rqz,companyId, rqq, rqz, rqq, rqz };

		result.setStatus(0);
		result.setMessage("成功");
		result.setBody(readService.findMapBySql(sb.toString(), params));
		return result;
	}
	
	/**
	 * 获取代理商佣金明细详情（按放款金额）
	 * 
	 * @param request
	 * @return
	 * @throws Exception
	 */
	public ResultInfo queryAgentMoneyDetail(HttpServletRequest request, JSONObject reqJson) throws Exception {
		ResultInfo result = new ResultInfo();
		Long companyId = reqJson.getLong("companyId");
		Long myAgentId = reqJson.getLong("myAgentId");
		Long agentId = reqJson.getLong("agentId");
		String rqq = reqJson.getString("rqq");
		String rqz = reqJson.getString("rqz");

		// 拼接检索条件
		StringBuffer where = new StringBuffer("");
		List<Object> params = new ArrayList<Object>();
		params.add(companyId);
		if (myAgentId != null && myAgentId>0){
			where.append(" and (d.id=? or d.fid=?)");
			params.add(myAgentId);
			params.add(myAgentId);
		}
		if (agentId != null && agentId>0){
			where.append(" and d.id=?");
			params.add(agentId);
		}
		if (!CommonUtil.isEmpty(rqq)) {
			where.append(" and a.create_time >= ?");
			params.add(rqq);
		}
		if (!CommonUtil.isEmpty(rqz)) {
			rqz = rqz.substring(0, 10) + " 23:59:59";
			where.append(" and a.create_time <= ?");
			params.add(rqz);
		}

		// 取得总记录行数
		String sql = "select count(1) cnt from t_company_agent_money a,t_loan b,t_customer c, t_company_agent d where a.money_type=2 and a.loan_id=b.id and b.customer_id=c.id and a.agent_id=d.id and d.company_id=?"
				+ where.toString();
		// 列表数据SQL: 把最需要审核的放在前面
		StringBuffer sb = new StringBuffer("");
		if (CommonUtil.getIntValue(reqJson.getString("hidePhone")) > 0)
			sb.append("select REPLACE(c.phone_no, SUBSTR(c.phone_no,4,4), '****') custPhone,c.name custName,b.req_money reqMoney,a.create_time createTime,");
		else
			sb.append("select c.phone_no custPhone,c.name custName,b.req_money reqMoney,a.create_time createTime,");
		sb.append(" d.agent_name agentName,(a.money+ifnull(a.sub_agent_money,0)) agentMoney,a.sub_agent_money subAgentMoney,")
		  .append(" (case when a.sub_agent_id>0 then (select m.agent_name from t_company_agent m where m.id=a.sub_agent_id) else null end) subAgentName")
		  .append(" from t_company_agent_money a,t_loan b,t_customer c, t_company_agent d where a.money_type=2 and a.loan_id=b.id and b.customer_id=c.id and a.agent_id=d.id and d.company_id=?")
		  .append(where.toString()).append(" order by a.id asc");

		result.setStatus(0);
		result.setMessage("成功");
		result.setBody(readService.pageQueryBySql(sb.toString(), sql, reqJson.getIntValue("pageSize"),
				reqJson.getIntValue("pageNo"), params.toArray()));
		return result;
	}
	
	/**
	 * 引流成功明细详情<按注册客户>
	 * 
	 * @param request
	 * @return
	 * @throws Exception
	 */
	public ResultInfo queryAgentCpaMoneyDetail(HttpServletRequest request, JSONObject reqJson) throws Exception {
		ResultInfo result = new ResultInfo();
		Long companyId = reqJson.getLong("companyId");
		Long myAgentId = reqJson.getLong("myAgentId");
		Long agentId = reqJson.getLong("agentId");
		String rqq = reqJson.getString("rqq");
		String rqz = reqJson.getString("rqz");

		// 拼接检索条件
		StringBuffer where = new StringBuffer("");
		List<Object> params = new ArrayList<Object>();
		params.add(companyId);
		if (myAgentId != null && myAgentId>0){
			where.append(" and (d.id=? or d.fid=?)");
			params.add(myAgentId);
			params.add(myAgentId);
		}
		if (agentId != null && agentId>0){
			where.append(" and d.id=?");
			params.add(agentId);
		}
		if (!CommonUtil.isEmpty(rqq)) {
			where.append(" and a.create_time >= ?");
			params.add(rqq);
		}
		if (!CommonUtil.isEmpty(rqz)) {
			rqz = rqz.substring(0, 10) + " 23:59:59";
			where.append(" and a.create_time <= ?");
			params.add(rqz);
		}

		// 取得总记录行数
		String sql = "select count(1) cnt from t_company_agent_money a,t_customer c, t_company_agent d where a.money_type=1 and a.customer_id=c.id and a.agent_id=d.id and d.company_id=?"
				+ where.toString();
		// 列表数据SQL: 把最需要审核的放在前面
		StringBuffer sb = new StringBuffer("");
		if (CommonUtil.getIntValue(reqJson.getString("hidePhone")) > 0)
			sb.append("select REPLACE(c.phone_no, SUBSTR(c.phone_no,4,4), '****') custPhone,c.name custName,a.create_time createTime,");
		else
			sb.append("select c.phone_no custPhone,c.name custName,a.create_time createTime,");
		sb.append(" d.agent_name agentName,(a.money+ifnull(a.sub_agent_money,0)) agentMoney,a.sub_agent_money subAgentMoney,")
		  .append(" (case when a.sub_agent_id>0 then (select m.agent_name from t_company_agent m where m.id=a.sub_agent_id) else null end) subAgentName")
		  .append(" from t_company_agent_money a,t_customer c, t_company_agent d where a.money_type=1 and a.customer_id=c.id and a.agent_id=d.id and d.company_id=?")
		  .append(where.toString()).append(" order by a.id asc");

		result.setStatus(0);
		result.setMessage("成功");
		result.setBody(readService.pageQueryBySql(sb.toString(), sql, reqJson.getIntValue("pageSize"),
				reqJson.getIntValue("pageNo"), params.toArray()));
		return result;
	}
	
	/**
	 * 财务日报表
	 * 
	 * @param request
	 * @return
	 * @throws Exception
	 */
	public ResultInfo queryDailyReport(HttpServletRequest request, JSONObject reqJson) throws Exception {
		ResultInfo result = new ResultInfo();
		String rqq = reqJson.getString("rqq");
		String rqz = reqJson.getString("rqz");

		// 拼接检索条件
		StringBuffer where = new StringBuffer("");
		List<Object> params = new ArrayList<Object>();
		if (!CommonUtil.isEmpty(rqq)) {
			where.append(" and a.rq >= ?");
			params.add(rqq);
		}
		if (!CommonUtil.isEmpty(rqz)) {
			rqz = rqz.substring(0, 10) + " 23:59:59";
			where.append(" and a.rq <= ?");
			params.add(rqz);
		}

		// 取得总记录行数
		String sql = "select count(1) cnt from t_count_oper_stats_day a where 1=1"
				+ where.toString();
		// 列表数据SQL: 把最需要审核的放在前面
		StringBuffer sb = new StringBuffer("");
		sb.append("select a.rq,a.loan_num loanNum, a.loan_money loanMoney,a.down_num downNum,a.down_money downMoney,loan_num2 xjNum,loan_money2 xjMoney,")
		  .append(" a.handle_fee handleFee, a.repay_num repayNum,a.repay_money repayMoney,a.repay_money2 repayLatefee,a.repay_bj repayBj,a.repay_lx repayLx,")
		  .append(" a.delay_num delayNum,a.delay_money delayMoney,a.bad_num badNum,a.bad_money badMoney,(a.agent_money+a.agent_money1) money")
		  .append(" from t_count_oper_stats_day a where 1=1")
		  .append(where.toString()).append(" order by a.rq desc");

		result.setStatus(0);
		result.setMessage("成功");
		result.setBody(readService.pageQueryBySql(sb.toString(), sql, reqJson.getIntValue("pageSize"),
				reqJson.getIntValue("pageNo"), params.toArray()));
		return result;
	}
	
	/**
	 * 实名认证统计表
	 * 
	 * @param request
	 * @return
	 * @throws Exception
	 */
	public ResultInfo countUserCertify(HttpServletRequest request, JSONObject reqJson) throws Exception {
		ResultInfo result = new ResultInfo();
		Long companyId = reqJson.getLong("companyId");
		String rqq = reqJson.getString("rqq");
		String rqz = reqJson.getString("rqz");
		if (companyId == null)
			companyId = 0L;
		// 拼接检索条件
		StringBuffer where = new StringBuffer("");
		List<Object> params = new ArrayList<Object>();
		params.add(companyId);
		if (!CommonUtil.isEmpty(rqq)) {
			where.append(" and a.audit_time >= ?");
			params.add(rqq);
		}
		if (!CommonUtil.isEmpty(rqz)) {
			rqz = rqz.substring(0, 10) + " 23:59:59";
			where.append(" and a.audit_time <= ?");
			params.add(rqz);
		}

		// 取得总记录行数
		StringBuffer sbRow = new StringBuffer("");
		sbRow.append("select count(1) cnt")
		  .append(" from t_sys_user a left join")
		  .append(" (select audit_id, count(1) sumNum,")
		  .append(" ifnull(sum(case when audit_status=1 then 1 else 0 end),0) sucNum,ifnull(sum(case when a.audit_status<>1 then 1 else 0 end),0) failNum")
		  .append(" from t_customer_certify a where a.company_id=? and a.audit_status>0 "+where.toString()+" group by audit_id) b on a.id=b.audit_id")
		  .append(" where a.company_id="+companyId+" and a.is_used=1 and a.agent_id is null");
		// 列表数据SQL: 把最需要审核的放在前面
		StringBuffer sb = new StringBuffer("");
		sb.append("select a.id userId,a.user_name userName,a.login_name loginName,ifnull(b.sumNum,0) sumNum,ifnull(b.sucNum,0) sucNum,ifnull(b.failNum,0) failNum")
		  .append(" from t_sys_user a left join")
		  .append(" (select audit_id, count(1) sumNum,")
		  .append(" ifnull(sum(case when audit_status=1 then 1 else 0 end),0) sucNum,ifnull(sum(case when a.audit_status<>1 then 1 else 0 end),0) failNum")
		  .append(" from t_customer_certify a where a.company_id=? and a.audit_status>0 "+where.toString()+" group by audit_id) b on a.id=b.audit_id")
		  .append(" where a.company_id="+companyId+" and a.is_used=1 and a.agent_id is null")
		  .append(" order by b.sumNum desc");

		result.setStatus(0);
		result.setMessage("成功");
		result.setBody(readService.pageQueryBySql(sb.toString(), sbRow.toString(), reqJson.getIntValue("pageSize"),
				reqJson.getIntValue("pageNo"), params.toArray()));
		return result;
	}
	
	/**
	 * 货款审核统计表
	 * 
	 * @param request
	 * @return
	 * @throws Exception
	 */
	public ResultInfo countUserLoanAudit(HttpServletRequest request, JSONObject reqJson) throws Exception {
		ResultInfo result = new ResultInfo();
		Long companyId = reqJson.getLong("companyId");
		String rqq = reqJson.getString("rqq");
		String rqz = reqJson.getString("rqz");
		if (companyId == null)
			companyId = 0L;
		// 拼接检索条件
		StringBuffer where = new StringBuffer("");
		List<Object> params = new ArrayList<Object>();
		params.add(companyId);
		if (!CommonUtil.isEmpty(rqq)) {
			where.append(" and a.audit_time >= ?");
			params.add(rqq);
		}
		if (!CommonUtil.isEmpty(rqz)) {
			rqz = rqz.substring(0, 10) + " 23:59:59";
			where.append(" and a.audit_time <= ?");
			params.add(rqz);
		}

		// 取得总记录行数
		StringBuffer sbRow = new StringBuffer("");
		sbRow.append("select count(1) cnt")
		  .append(" from t_sys_user a left join")
		  .append(" (select auditor_id,count(1) sumNum,")
		  .append(" ifnull(sum(case when a.loan_status>1 then 1 else 0 end),0) sucNum,")
		  .append(" ifnull(sum(case when a.loan_status=1 then 1 else 0 end),0) failNum,")
		  .append(" ifnull(sum(case when ifnull(a.payback_date,now())>a.end_time then 1 else 0 end),0) delayNum,")
		  .append(" ifnull(sum(case when a.loan_status=6 then 1 else 0 end),0) badNum")
		  .append(" from t_loan a where a.company_id=? and a.loan_status>0 "+where.toString()+" group by auditor_id) b on a.id=b.auditor_id")
		  .append(" where a.company_id="+companyId+" and a.is_used=1 and a.is_auditor=1");
		// 列表数据SQL: 把最需要审核的放在前面
		StringBuffer sb = new StringBuffer("");
		sb.append("select a.id userId,a.user_name userName,a.login_name loginName,ifnull(b.sumNum,0) sumNum,ifnull(b.sucNum,0) sucNum,")
		  .append(" ifnull(b.failNum,0) failNum,ifnull(b.delayNum,0) delayNum,ifnull(b.badNum,0) badNum")
		  .append(" from t_sys_user a left join")
		  .append(" (select auditor_id,count(1) sumNum,")
		  .append(" ifnull(sum(case when a.loan_status>1 then 1 else 0 end),0) sucNum,")
		  .append(" ifnull(sum(case when a.loan_status=1 then 1 else 0 end),0) failNum,")
		  .append(" ifnull(sum(case when ifnull(a.payback_date,now())>a.end_time then 1 else 0 end),0) delayNum,")
		  .append(" ifnull(sum(case when a.loan_status=6 then 1 else 0 end),0) badNum")
		  .append(" from t_loan a where a.company_id=? and a.loan_status>0 "+where.toString()+" group by auditor_id) b on a.id=b.auditor_id")
		  .append(" where a.company_id="+companyId+" and a.is_used=1 and a.is_auditor=1")
		  .append(" order by b.sumNum desc");

		result.setStatus(0);
		result.setMessage("成功");
		result.setBody(readService.pageQueryBySql(sb.toString(), sbRow.toString(), reqJson.getIntValue("pageSize"),
				reqJson.getIntValue("pageNo"), params.toArray()));
		return result;
	}
	
	/**
	 * 催收统计表
	 * 
	 * @param request
	 * @return
	 * @throws Exception
	 */
	public ResultInfo countLoanOverdue(HttpServletRequest request, JSONObject reqJson) throws Exception {
		ResultInfo result = new ResultInfo();
		Long companyId = reqJson.getLong("companyId");
		String rqq = reqJson.getString("rqq");
		String rqz = reqJson.getString("rqz");
		if (companyId == null)
			companyId = 0L;
		// 拼接检索条件
		StringBuffer where = new StringBuffer("");
		List<Object> params = new ArrayList<Object>();
		params.add(companyId);
		if (!CommonUtil.isEmpty(rqq)) {
			where.append(" and b.begin_time >= ?");
			params.add(rqq);
		}
		if (!CommonUtil.isEmpty(rqz)) {
			rqz = rqz.substring(0, 10) + " 23:59:59";
			where.append(" and b.begin_time <= ?");
			params.add(rqz);
		}

		// 取得总记录行数
		StringBuffer sbRow = new StringBuffer("");
		sbRow.append("select count(1) cnt")	
		  .append(" from t_sys_user a left join (")
		  .append(" select b.overduer_id")
		  .append(" from t_loan a, t_loan_overdue_assignlog b")
		  .append(" where a.id=b.loan_id and a.company_id=?").append(where.toString())
		  .append(" group by b.overduer_id) b on a.id=b.overduer_id")
		  .append(" where a.company_id="+companyId+" and a.is_used=1 and a.is_overduer=1");
		// 列表数据SQL: 把最需要审核的放在前面
		StringBuffer sb = new StringBuffer("");
		sb.append("select a.id userId,a.user_name userName,a.login_name loginName,ifnull(b.sumNum,0) sumNum,ifnull(b.sucNum,0) sucNum,ifnull(b.sucMoney,0) sucMoney,(ifnull(b.sumMoney,0) - ifnull(b.sucMoney,0)) failMoney,")
		  .append(" (case when (b.sumMoney)=0 then 0 else floor((ifnull(b.sumMoney,0) - ifnull(b.sucMoney,0))/(b.sumMoney) * 10000) end) badPercent")	
		  .append(" from t_sys_user a left join (")
		  .append(" select b.overduer_id, count(1) sumNum,sum(a.req_money) sumMoney,")
		  .append(" ifnull(sum(case when a.loan_status=5 and a.payback_date>=b.begin_time and (a.payback_date<b.end_time or b.end_time is null) then 1 else 0 end),0) sucNum,")
		  .append(" ifnull(sum(case when a.loan_status=5 and a.payback_date>=b.begin_time and (a.payback_date<b.end_time or b.end_time is null) then a.req_money else 0 end),0) sucMoney")
		  .append(" from t_loan a, t_loan_overdue_assignlog b")
		  .append(" where a.id=b.loan_id and a.company_id=?").append(where.toString())
		  .append(" group by b.overduer_id) b on a.id=b.overduer_id")
		  .append(" where a.company_id="+companyId+" and a.is_used=1 and a.is_overduer=1")
		  .append(" order by b.sumNum desc");

		result.setStatus(0);
		result.setMessage("成功");
		result.setBody(readService.pageQueryBySql(sb.toString(), sbRow.toString(), reqJson.getIntValue("pageSize"),
				reqJson.getIntValue("pageNo"), params.toArray()));
		return result;
	}
	
	/**
	 * 注册地址监控
	 * 
	 * @param request
	 * @return
	 * @throws Exception
	 */
	public ResultInfo queryCityDiffer(HttpServletRequest request, JSONObject reqJson) throws Exception {
		ResultInfo result = new ResultInfo();
		Long companyId = reqJson.getLong("companyId");
		String rqq = reqJson.getString("rqq");
		String rqz = reqJson.getString("rqz");
		if (companyId == null)
			companyId = 0L;
		// 拼接检索条件
		StringBuffer where = new StringBuffer("");
		List<Object> params = new ArrayList<Object>();
		params.add(companyId);
		if (!CommonUtil.isEmpty(rqq)) {
			where.append(" and a.audit_time >= ?");
			params.add(rqq);
		}
		if (!CommonUtil.isEmpty(rqz)) {
			rqz = rqz.substring(0, 10) + " 23:59:59";
			where.append(" and a.audit_time <= ?");
			params.add(rqz);
		}

		// 取得总记录行数
		StringBuffer sbRow = new StringBuffer("");
		sbRow.append("select count(1) cnt")
		  .append(" from t_customer_certify a,t_customer b")
		  .append(" where a.customer_id=b.id and a.reg_city<>a.card_city and b.company_id=?")
		  .append(" and exists (select 1 from t_loan b where b.customer_id=a.customer_id and now()>b.end_time and b.loan_status in (3,6))")
		  .append(where.toString());
		// 列表数据SQL: 把最需要审核的放在前面
		StringBuffer sb = new StringBuffer("");
		sb.append("select b.id custId,b.phone_no custPhone,b.name custName,a.reg_address regAddress,a.card_city cardCity,")
		  .append(" ifnull(b.authed_credit,0) authedCredit,")	
		  .append(" (select count(1) from t_loan c where c.customer_id=b.id) loanNum,")
		  .append(" (select count(1) from t_loan c where c.customer_id=b.id and c.loan_status in (3,6) and now()>c.end_time) overdueNum")
		  .append(" from t_customer_certify a,t_customer b")
		  .append(" where a.customer_id=b.id and a.reg_city<>a.card_city and b.company_id=?")
		  .append(" and exists (select 1 from t_loan b where b.customer_id=a.customer_id and now()>b.end_time and b.loan_status in (3,6))")
		  .append(where.toString()).append(" order by a.id asc");

		result.setStatus(0);
		result.setMessage("成功");
		result.setBody(readService.pageQueryBySql(sb.toString(), sbRow.toString(), reqJson.getIntValue("pageSize"),
				reqJson.getIntValue("pageNo"), params.toArray()));
		return result;
	}
	
	/**
	 * 手机设备使用监控
	 * 
	 * @param request
	 * @return
	 * @throws Exception
	 */
	public ResultInfo queryPhoneDiff(HttpServletRequest request, JSONObject reqJson) throws Exception {
		ResultInfo result = new ResultInfo();
		Long companyId = reqJson.getLong("companyId");
		String rqq = reqJson.getString("rqq");
		String rqz = reqJson.getString("rqz");
		int accounts = CommonUtil.getIntValue(reqJson.getString("accounts"));
		if (companyId == null)
			companyId = 0L;
		// 拼接检索条件
		StringBuffer where = new StringBuffer("");
		List<Object> params = new ArrayList<Object>();
		params.add(companyId);
		if (!CommonUtil.isEmpty(rqq)) {
			where.append(" and a.req_date >= ?");
			params.add(rqq);
		}
		if (!CommonUtil.isEmpty(rqz)) {
			rqz = rqz.substring(0, 10) + " 23:59:59";
			where.append(" and a.req_date <= ?");
			params.add(rqz);
		}
		params.add(accounts);

		// 取得总记录行数
		StringBuffer sbRow = new StringBuffer("");
		sbRow.append("select count(1) cnt")
		  .append(" from (select distinct a.client_id,a.customer_id")
		  .append(" from t_loan a where a.company_id=? "+where.toString()+") a group by a.client_id having count(*)>?");
		// 列表数据SQL: 把最需要审核的放在前面
		StringBuffer sb = new StringBuffer("");
		sb.append("select a.client_id clientid,count(1) accounts,")
		  .append(" (select group_concat(b.phone_no) from t_customer b where b.id=a.customer_id) custPhones,")	
		  .append(" (select count(1) from t_loan b where b.client_id=a.client_id) loanNum,")
		  .append(" (select ifnull(sum(b.req_money),0) from t_loan b where b.client_id=a.client_id) loanMoney")
		  .append(" from (select distinct a.client_id,a.customer_id")
		  .append(" from t_loan a where a.company_id=? "+where.toString()+") a group by a.client_id having count(*)>?")
		  .append(" order by a.client_id asc");

		result.setStatus(0);
		result.setMessage("成功");
		result.setBody(readService.pageQueryBySql(sb.toString(), sbRow.toString(), reqJson.getIntValue("pageSize"),
				reqJson.getIntValue("pageNo"), params.toArray()));
		return result;
	}


	/**
	 * 花呗认证统计表
	 *
	 * @param request
	 * @return
	 * @throws Exception
	 */
	public ResultInfo countZfbCertify(HttpServletRequest request, JSONObject reqJson) throws Exception {
		ResultInfo result = new ResultInfo();
		Long companyId = reqJson.getLong("companyId");
		String rqq = reqJson.getString("rqq");
		String rqz = reqJson.getString("rqz");
		if (companyId == null)
			companyId = 0L;
		// 拼接检索条件
		StringBuffer where = new StringBuffer("");
		List<Object> params = new ArrayList<Object>();
		params.add(companyId);
		if (!CommonUtil.isEmpty(rqq)) {
			where.append(" and a.audit_time >= ?");
			params.add(rqq);
		}
		if (!CommonUtil.isEmpty(rqz)) {
			rqz = rqz.substring(0, 10) + " 23:59:59";
			where.append(" and a.audit_time <= ?");
			params.add(rqz);
		}

		// 取得总记录行数
		StringBuffer sbRow = new StringBuffer("");
		sbRow.append("select count(1) cnt")
				.append(" from t_sys_user a left join")
				.append(" (select audit_id, count(1) sumNum,")
				.append(" ifnull(sum(case when audit_status=1 then 1 else 0 end),0) sucNum,ifnull(sum(case when a.audit_status<>1 then 1 else 0 end),0) failNum")
				.append(" from t_customer_zfb a where a.company_id=? and a.audit_status>0 "+where.toString()+" group by audit_id) b on a.id=b.audit_id")
				.append(" where a.company_id="+companyId+" and a.is_used=1 and a.agent_id is null");
		// 列表数据SQL: 把最需要审核的放在前面
		StringBuffer sb = new StringBuffer("");
		sb.append("select a.id userId,a.user_name userName,a.login_name loginName,ifnull(b.sumNum,0) sumNum,ifnull(b.sucNum,0) sucNum,ifnull(b.failNum,0) failNum")
				.append(" from t_sys_user a left join")
				.append(" (select audit_id, count(1) sumNum,")
				.append(" ifnull(sum(case when audit_status=1 then 1 else 0 end),0) sucNum,ifnull(sum(case when a.audit_status<>1 then 1 else 0 end),0) failNum")
				.append(" from t_customer_zfb a where a.company_id=? and a.audit_status>0 "+where.toString()+" group by audit_id) b on a.id=b.audit_id")
				.append(" where a.company_id="+companyId+" and a.is_used=1 and a.agent_id is null")
				.append(" order by b.sumNum desc");
		result.setStatus(0);
		result.setMessage("成功");
		result.setBody(readService.pageQueryBySql(sb.toString(), sbRow.toString(), reqJson.getIntValue("pageSize"),
				reqJson.getIntValue("pageNo"), params.toArray()));
		return result;
	}
}
